package multi;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.*;
import java.util.Calendar;
import java.util.Random;

import javax.sql.*;

public class SpatialMultiTest implements Runnable {

	public void run() {
		System.out.println("Started: " + Thread.currentThread().getName());
		
		if (Constants.USE_STORED_PROC)
			findGeoLocationsWithStoredProc();
		else
			findGeoLocationsDirectQuery();
		
		System.out.println("Done: " + Thread.currentThread().getName());
	} 

	public void findGeoLocationsWithStoredProc() {
		System.out.println("Using Stored Procedure");
		Connection con = null;
		try {

			con = Constants.getConnection();
			Statement stmt = con.createStatement();

			String query;

			if (Constants.IS_MYSQL_SPATIAL_TEST)
				query = Constants.MYSQL_SPATIAL_QUERY;
			else
				query = Constants.HAVERSINE_QUERY;

			System.out.println("Using Query: " + query);

			for (int i = 0; i < Constants.NO_OF_LOOPS_PER_THREAD; i++) {

				// stmt.execute(query1);
				CallableStatement cs = con.prepareCall("{ " + query + "}");
				long startTime = Calendar.getInstance().getTimeInMillis();
				if (cs.execute()) {
					long timeTaken = Calendar.getInstance().getTimeInMillis()
							- startTime;
					Constants.addTotalTime(timeTaken);
					System.out.println(Thread.currentThread().getName()
							+ ": Time Taken: " + timeTaken + " milliseconds");
					ResultSet rs = cs.getResultSet();

					int rows = 0;
					while (rs.next()) {
						rows++;
						// System.out.println ("RS: Id: " + rs.getInt("id") +
						// " Lat: " + rs.getDouble("latitude") + " Lon: " +
						// rs.getDouble("longitude") + " Dist: " +
						// rs.getDouble("distance"));
					}
					System.out.println("Total Rows: " + rows);
				}

			}

		} // end try

		catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Constants.closeConnection(con);
		}
	}

	
	public void findGeoLocationsDirectQuery() {
		System.out.println("Using Direct Query");
		Connection con = null;
		try {

			con = Constants.getConnection();
			
			//Statement stmt = con.createStatement();
			String query="";
			double lat=Constants.LAT;
			double lon=Constants.LON;
			double radius=Constants.RADIUS_DEGREE_UNITS;
			
			if (Constants.IS_MYSQL_SPATIAL_TEST)
			{
				double x1 = lat - radius; double y1 = lon - radius;
				double x2 = lat + radius; double y2 = lon - radius;
				double x3 = lat + radius; double y3 = lon + radius;
				double x4 = lat - radius; double y4 = lon + radius;
				String polygonStr = x1 + " " + y1 + "," + x2 + " " + y2 + "," + x3 + " " + y3 + "," + x4 + " " + y4 + "," + x1 + " " + y1;
				String pointStr = lat + " " + lon;
				
				query = "SELECT id, latitude, longitude, AsText(location),"; 
				query = query + " SQRT(POW( ABS( X(location) - " +  lat + "), 2) +"; 
				query = query + " POW( ABS(Y(location) - " + lon + "), 2 )) AS distance"; 
				query = query + " FROM geocode "; 
				query = query + " WHERE Intersects( location, GeomFromText('POLYGON((" + polygonStr + "))'))"; 
				query = query + " AND (SQRT(POW( ABS( X(location) - X(GeomFromText('POINT(" + pointStr + ")'))), 2) +";
				query = query + " POW( ABS(Y(location) - Y(GeomFromText('POINT(" + pointStr + ")'))), 2 ))) < " + radius ;
				//query = query + " order by distance";
			}
			else
			{			
				query = "select g.id, g.latitude, g.longitude, (3956 * 2 * ASIN ( SQRT ( POWER(SIN(("+lat+" - g.latitude)*pi()/180 / 2),";
				query = query + " 2) + COS(" + lat + "* pi()/180) * COS(g.latitude * pi()/180) * POWER(SIN((" + lon + "- g.longitude) *";
				query = query + " pi()/180 / 2), 2) ) ))/69 as distance" ;
				query = query + " from geocode g";
				query = query + " where g.longitude between (" + lon + " - " + radius + "/abs(cos(radians(" + lat + "))))";  
				query = query + " and (" + lon + " + " + radius + "/abs(cos(radians(" + lat + ")))";
				query = query + " ) and g.latitude between (" + lat + " - " + radius + ") and (" + lat + "+" + radius + ")";
				query = query + " having distance < " + radius; 
				//query = query + " order by distance";
			}
			
			System.out.println("Query to use: " + query);
			
			PreparedStatement pStmt = con.prepareStatement(query);
			ResultSet rs;
			for (int i = 0; i < Constants.NO_OF_LOOPS_PER_THREAD; i++) {
				long startTime = Calendar.getInstance().getTimeInMillis();
				 rs = pStmt.executeQuery();
				 long timeTaken = Calendar.getInstance().getTimeInMillis()
					- startTime;
				 Constants.addTotalTime(timeTaken);
				 System.out.println(Thread.currentThread().getName()
					+ ": Time Taken: " + timeTaken + " milliseconds");
			
				int rows = 0;
				while (rs.next()) {
					rows++;
					/*
					System.out.println ("RS: Id: " + rs.getInt("id") +
					" Lat: " + rs.getDouble("latitude") + " Lon: " +
					rs.getDouble("longitude") + " Dist: " +
					rs.getDouble("distance"));
					*/
				}
				System.out.println("Total Rows: " + rows);
			}
		
		} // end try

		catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Constants.closeConnection(con);
		}
	}

} // end class
